using Homework8.Models;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;

namespace Homework8.Services
{
    public class ClassService
    {
        public List<Class> GetAllClasses()
        {
            List<Class> classes = new List<Class>();
            
            using var connection = DBHelper.GetConnection();
            connection.Open();
            
            string sql = "SELECT * FROM Class";
            using var command = new MySqlCommand(sql, connection);
            using var reader = command.ExecuteReader();
            
            while (reader.Read())
            {
                classes.Add(new Class
                {
                    ClassId = reader.GetInt32("ClassId"),
                    Name = reader.GetString("Name"),
                    Grade = reader.IsDBNull(reader.GetOrdinal("Grade")) ? null : reader.GetInt32("Grade"),
                    SchoolId = reader.GetInt32("SchoolId"),
                    ClassTeacher = reader.IsDBNull(reader.GetOrdinal("ClassTeacher")) ? null : reader.GetString("ClassTeacher")
                });
            }
            
            return classes;
        }
        
        public Class? GetClassById(int classId)
        {
            using var connection = DBHelper.GetConnection();
            connection.Open();
            
            string sql = "SELECT * FROM Class WHERE ClassId = @ClassId";
            using var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@ClassId", classId);
            
            using var reader = command.ExecuteReader();
            
            if (reader.Read())
            {
                return new Class
                {
                    ClassId = reader.GetInt32("ClassId"),
                    Name = reader.GetString("Name"),
                    Grade = reader.IsDBNull(reader.GetOrdinal("Grade")) ? null : reader.GetInt32("Grade"),
                    SchoolId = reader.GetInt32("SchoolId"),
                    ClassTeacher = reader.IsDBNull(reader.GetOrdinal("ClassTeacher")) ? null : reader.GetString("ClassTeacher")
                };
            }
            
            return null;
        }
        
        public List<Class> GetClassesBySchoolId(int schoolId)
        {
            List<Class> classes = new List<Class>();
            
            using var connection = DBHelper.GetConnection();
            connection.Open();
            
            string sql = "SELECT * FROM Class WHERE SchoolId = @SchoolId";
            using var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@SchoolId", schoolId);
            
            using var reader = command.ExecuteReader();
            
            while (reader.Read())
            {
                classes.Add(new Class
                {
                    ClassId = reader.GetInt32("ClassId"),
                    Name = reader.GetString("Name"),
                    Grade = reader.IsDBNull(reader.GetOrdinal("Grade")) ? null : reader.GetInt32("Grade"),
                    SchoolId = reader.GetInt32("SchoolId"),
                    ClassTeacher = reader.IsDBNull(reader.GetOrdinal("ClassTeacher")) ? null : reader.GetString("ClassTeacher")
                });
            }
            
            return classes;
        }
        
        public void AddClass(Class cls)
        {
            using var connection = DBHelper.GetConnection();
            connection.Open();
            
            string sql = "INSERT INTO Class (Name, Grade, SchoolId, ClassTeacher) VALUES (@Name, @Grade, @SchoolId, @ClassTeacher); SELECT LAST_INSERT_ID();";
            using var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@Name", cls.Name);
            command.Parameters.AddWithValue("@Grade", cls.Grade ?? (object)DBNull.Value);
            command.Parameters.AddWithValue("@SchoolId", cls.SchoolId);
            command.Parameters.AddWithValue("@ClassTeacher", cls.ClassTeacher ?? (object)DBNull.Value);
            
            var classId = Convert.ToInt32(command.ExecuteScalar());
            cls.ClassId = classId;
            
            // 记录操作日志
            DBHelper.LogOperation("添加", "Class", classId, $"添加班级：{cls.Name}");
        }
        
        public void UpdateClass(Class cls)
        {
            using var connection = DBHelper.GetConnection();
            connection.Open();
            
            string sql = "UPDATE Class SET Name = @Name, Grade = @Grade, SchoolId = @SchoolId, ClassTeacher = @ClassTeacher WHERE ClassId = @ClassId";
            using var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@ClassId", cls.ClassId);
            command.Parameters.AddWithValue("@Name", cls.Name);
            command.Parameters.AddWithValue("@Grade", cls.Grade ?? (object)DBNull.Value);
            command.Parameters.AddWithValue("@SchoolId", cls.SchoolId);
            command.Parameters.AddWithValue("@ClassTeacher", cls.ClassTeacher ?? (object)DBNull.Value);
            
            command.ExecuteNonQuery();
            
            // 记录操作日志
            DBHelper.LogOperation("修改", "Class", cls.ClassId, $"修改班级：{cls.Name}");
        }
        
        public bool DeleteClass(int classId)
        {
            using var connection = DBHelper.GetConnection();
            connection.Open();
            
            // 检查是否有关联的学生
            string checkSql = "SELECT COUNT(*) FROM Student WHERE ClassId = @ClassId";
            using (var checkCommand = new MySqlCommand(checkSql, connection))
            {
                checkCommand.Parameters.AddWithValue("@ClassId", classId);
                var count = Convert.ToInt32(checkCommand.ExecuteScalar());
                if (count > 0)
                {
                    return false; // 有关联学生，不能删除
                }
            }
            
            // 获取班级名称用于日志
            string className = "";
            using (var nameCommand = new MySqlCommand("SELECT Name FROM Class WHERE ClassId = @ClassId", connection))
            {
                nameCommand.Parameters.AddWithValue("@ClassId", classId);
                var nameResult = nameCommand.ExecuteScalar();
                className = nameResult != null ? nameResult.ToString() ?? "" : "";
            }
            
            // 执行删除
            string sql = "DELETE FROM Class WHERE ClassId = @ClassId";
            using var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@ClassId", classId);
            
            int result = command.ExecuteNonQuery();
            
            if (result > 0)
            {
                // 记录操作日志
                DBHelper.LogOperation("删除", "Class", classId, $"删除班级：{className}");
                return true;
            }
            
            return false;
        }
    }
} 